package com.caifu.mapper;

import com.caifu.pojo.YlMonthBill;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 月结算表 Mapper 接口
 * </p>
 *
 * @author GZ
 * @since 2020-11-06
 */
public interface YlMonthBillMapper extends BaseMapper<YlMonthBill> {
    /**
    * 
    * 功能描述：根据年份或月份获取养老机构账务统计信息
    * 
    * @Param pensionNo 养老机构编号   year 年份  month 月份
    * @Return      
    * @Exception   
    * @Author  GZ
    * @Date   2020/11/26 10:47
    */
    Map<String,Object>  queryAllBillInfoByYearAndMonth(@Param("pensionNo") String pensionNo
            ,@Param("year") String year,@Param("month") String month);
    /**
    * 
    * 功能描述：根据年份和月份获取养老机构总缴费信息
    * 
    * @Param pensionNo 养老机构编号  year 年份   month 月份
    * @Return      
    * @Exception   
    * @Author  GZ
    * @Date   2020/11/26 13:33
    */
    String queryTotalPaymentAmount(@Param("pensionNo") String pensionNo
            ,@Param("year") String year,@Param("month") String month);
    /**
    * 
    * 功能描述：查询当前欠费的老人信息列表
    * 
    * @Param pensionNo 养老机构编号 maxSize最大条数
    * @Return      
    * @Exception   
    * @Author  GZ
    * @Date   2020/11/26 15:45
    */
    List<Map<String,Object>> queryOwingMoneyUserInfo(@Param("pensionNo") String pensionNo
            ,@Param("maxSize") int maxSize);
    /**
    * 
    * 功能描述：查询用户欠费账单的最小日期
    * 
    * @Param
    * @Return      
    * @Exception   
    * @Author  GZ
    * @Date   2020/11/26 15:55
    */
    String queryMinOwingMoneyBillDate(@Param("userNo") String userNo);

    //获取用户总费用
    @Select("select IFNULL(sum(init_cur_month_fee),0) from yl_month_bill where user_no=#{userNo}")
    double getUserTotalAmt(@Param("userNo") String userNo);

    //获取用户月费用
    @Select(" select t.* from (" +
            " select a.month,IFNULL(b.cur_real_pay_fee,0) pay_fee, case when b.BILL_MONTH is null then 0 else 1 end as is_exist FROM " +
            " (select 1 as month from dual union select 2 as month from dual union select 3 as month from dual union select 4 as month from dual union select 5 as month from dual union select 6 as month from dual union select 7 as month from dual union select 8 as month from dual union select 9 as month from dual union select 10 as month from dual union select 11 as month from dual union select 12 as month from dual)a" +
            " left join (select * from yl_month_bill where USER_NO=#{userNo})b on a.month=b.BILL_MONTH)t order by t.month asc ")
    List<Map<String,Object>> listUserMonthAmt(@Param("userNo") String userNo);

}
